﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Data;
using System.Diagnostics;
using System.Text;
using System.Windows.Forms;
using Microsoft.Office.Server.Search.Administration;
using Microsoft.Office.Server.Search.Query;
using Microsoft.SharePoint;

namespace zevenseas.Community.Search.SQLSearchCoder
{
    public partial class SearchCoder : Form
    {
        public SearchCoder()
        {
            InitializeComponent();
        }

        private void btnConnect_Click(object sender, EventArgs e)
        {
            SearchContext context;
            errorProvider1.Clear();
            lblConnectError.Text = "";

            if (string.IsNullOrEmpty(txtWebApplicationUrl.Text))
            {
                errorProvider1.SetError(txtWebApplicationUrl, "Enter the URL to a Site Collection");
                lblConnectError.Text = "Enter the URL to a Site Collection";
                return;
            }

            try
            {
                using (SPSite site = new SPSite(txtWebApplicationUrl.Text))
                {
                    context = SearchContext.GetContext(site);
                    Scopes scopes = new Scopes(context);

                    foreach (Scope scope in scopes.AllScopes)
                    {
                        clbScopes.Items.Add(scope);
                    }
                    clbScopes.DisplayMember = "Name";

                    Schema sspSchema = new Schema(context);
                    Collection<ManagedProperty> searchProps = new Collection<ManagedProperty>();

                    foreach (ManagedProperty managedProperty in sspSchema.AllManagedProperties)
                    {
                        if (managedProperty.FullTextQueriable && managedProperty.Retrievable)
                            searchProps.Add(managedProperty);
                    }

                    BindingSource bindToSchemaW1 = new BindingSource();
                    bindToSchemaW1.DataSource = searchProps;
                    bindToSchemaW1.Filter = "FullTextQueriable = 1";
                    clbProperties.DataSource = bindToSchemaW1;
                    clbProperties.DisplayMember = "Name";

                    BindingSource bindToSchemaW2 = new BindingSource();
                    bindToSchemaW2.DataSource = searchProps;
                    bindToSchemaW2.Filter = "FullTextQueriable = 1";
                    cboWhereProp1.DataSource = bindToSchemaW2;
                    cboWhereProp1.DisplayMember = "Name";

                    BindingSource bindToSchemaW3 = new BindingSource();
                    bindToSchemaW3.DataSource = searchProps;
                    bindToSchemaW3.Filter = "FullTextQueriable = 1";
                    cboWhereProp2.DataSource = bindToSchemaW3;
                    cboWhereProp2.DisplayMember = "Name";

                    BindingSource bindToSchemaW4 = new BindingSource();
                    bindToSchemaW4.DataSource = searchProps;
                    bindToSchemaW4.Filter = "FullTextQueriable = 1";
                    cboWhereProp3.DataSource = bindToSchemaW4;
                    cboWhereProp3.DisplayMember = "Name";

                    BindingSource bindToSchemaW5 = new BindingSource();
                    bindToSchemaW5.DataSource = searchProps;
                    bindToSchemaW5.Filter = "FullTextQueriable = 1";
                    cboWhereProp21.DataSource = bindToSchemaW5;
                    cboWhereProp21.DisplayMember = "Name";

                    BindingSource bindToSchemaW6 = new BindingSource();
                    bindToSchemaW6.DataSource = searchProps;
                    bindToSchemaW6.Filter = "FullTextQueriable = 1";
                    cboWhereProp22.DataSource = bindToSchemaW6;
                    cboWhereProp22.DisplayMember = "Name";

                    BindingSource bindToSchemaW7 = new BindingSource();
                    bindToSchemaW7.DataSource = searchProps;
                    bindToSchemaW7.Filter = "FullTextQueriable = 1";
                    cboWhereProp23.DataSource = bindToSchemaW7;
                    cboWhereProp23.DisplayMember = "Name";

                    cboWhereConnector1.DataSource = Enum.GetValues(typeof(LogicalOperators));
                    cboWhereConnector2.DataSource = Enum.GetValues(typeof(LogicalOperators));
                    cboWhereConnector21.DataSource = Enum.GetValues(typeof(LogicalOperators));
                    cboWhereConnector22.DataSource = Enum.GetValues(typeof(LogicalOperators));

                    cboGroupOp.DataSource = Enum.GetValues(typeof(GroupLogicalOperators));
                }
            }
            catch (System.IO.FileNotFoundException)
            {
                errorProvider1.SetError(txtWebApplicationUrl, "Cannot find the site");
                lblConnectError.Text = "Cannot find the site";
            }
        }

        private void btnBuildSQL_Click(object sender, EventArgs e)
        {
            BuildSQL();

            tabMain.SelectedTab = tabPgRun;
        }

        private void BuildSQL()
        {
            errorProvider1.Clear();
            lblQueryError.Text = "";

            StringBuilder SQLCode = new StringBuilder();

            MOSSSQLSearch mossSearch = new MOSSSQLSearch();
            SQLCode.AppendLine("MOSSSQLSearch mossSearch = new MOSSSQLSearch();");

            SelectStatement selectStatement = new SelectStatement();
            SQLCode.AppendLine("SelectStatement selectStatement = new SelectStatement();");

            List<string> returnProperties = new List<string>();
            SQLCode.AppendLine("List<string> returnProperties = new List<string>();");

            List<string> Scopes = new List<string>();
            SQLCode.AppendLine("List<string> Scopes = new List<string>();");

            foreach (Scope scope in clbScopes.CheckedItems)
            {
                Scopes.Add(scope.Name);
                SQLCode.AppendLine(@"Scopes.Add(""" + scope.Name + @""");");
            }

            WhereStatement whereStatement = new WhereStatement();
            whereStatement.Scopes = Scopes;
            SQLCode.AppendLine("WhereStatement whereStatement = new WhereStatement();");
            SQLCode.AppendLine("whereStatement.Scopes = Scopes;");

            if (string.IsNullOrEmpty(whereStatement.Keywords))
            {
                whereStatement.Keywords = txtSearchTerms.Text;
                SQLCode.AppendLine(@"whereStatement.Keywords = """ + txtSearchTerms.Text + @""";");
                if (radPredicate2.Checked)
                {
                    whereStatement.KeywordsType = SearchTypes.FreeText;
                    SQLCode.AppendLine(@"whereStatement.KeywordsType = SearchTypes.FreeText;");
                }
                else
                {
                    whereStatement.KeywordsType = SearchTypes.Contains;
                    SQLCode.AppendLine(@"whereStatement.KeywordsType = SearchTypes.Contains;");
                }
            }

            WhereGroup whereGroup = new WhereGroup();
            SQLCode.AppendLine(@"WhereGroup whereGroup = new WhereGroup();");

            if (!string.IsNullOrEmpty(txtWhereText1.Text))
            {
                WherePropertyContains whereProperty = new WherePropertyContains();
                ManagedProperty prop = (ManagedProperty)cboWhereProp1.SelectedItem;
                whereProperty.Property = prop.Name;
                whereProperty.SearchTerm = txtWhereText1.Text;
                SQLCode.AppendLine(@"WherePropertyContains whereProperty = new WherePropertyContains();");
                SQLCode.AppendLine(@"whereProperty.Property = """ + prop.Name + @""";");
                SQLCode.AppendLine(@"whereProperty.SearchTerm = """ + txtWhereText1.Text + @""";");

                whereGroup.WhereProperties.Add(whereProperty);
                SQLCode.AppendLine(@"whereGroup.WhereProperties.Add(whereProperty);");

                if (!string.IsNullOrEmpty(txtWhereText2.Text))
                {
                    whereProperty = new WherePropertyContains();
                    prop = (ManagedProperty)cboWhereProp2.SelectedItem;
                    whereProperty.Property = prop.Name;
                    whereProperty.SearchTerm = txtWhereText2.Text;
                    whereProperty.PropertyOperator = (LogicalOperators)cboWhereConnector1.SelectedItem;
                    SQLCode.AppendLine(@"whereProperty = new WherePropertyContains();");
                    SQLCode.AppendLine(@"whereProperty.Property = """ + prop.Name + @""";");
                    SQLCode.AppendLine(@"whereProperty.SearchTerm = """ + txtWhereText2.Text + @""";");
                    SQLCode.AppendLine(@"whereProperty.PropertyOperator = LogicalOperators." + cboWhereConnector1.SelectedItem.ToString() + ";");

                    whereGroup.WhereProperties.Add(whereProperty);
                    SQLCode.AppendLine(@"whereGroup.WhereProperties.Add(whereProperty);");

                    if (!string.IsNullOrEmpty(txtWhereText3.Text))
                    {
                        whereProperty = new WherePropertyContains();
                        prop = (ManagedProperty)cboWhereProp3.SelectedItem;
                        whereProperty.Property = prop.Name;
                        whereProperty.SearchTerm = txtWhereText3.Text;
                        whereProperty.PropertyOperator = (LogicalOperators)cboWhereConnector2.SelectedItem;
                        SQLCode.AppendLine(@"whereProperty = new WherePropertyContains();");
                        SQLCode.AppendLine(@"whereProperty.Property = """ + prop.Name + @""";");
                        SQLCode.AppendLine(@"whereProperty.SearchTerm = """ + txtWhereText3.Text + @""";");
                        SQLCode.AppendLine(@"whereProperty.PropertyOperator = LogicalOperators." + cboWhereConnector2.SelectedItem.ToString() + ";");

                        whereGroup.WhereProperties.Add(whereProperty);
                        SQLCode.AppendLine(@"whereGroup.WhereProperties.Add(whereProperty);");
                    }
                }
                whereStatement.WhereGroups.Add(whereGroup);
            }

            if (!string.IsNullOrEmpty(txtWhereText21.Text))
            {
                WhereGroup whereGroup2 = new WhereGroup();
                whereGroup2.LogOperator = (GroupLogicalOperators)cboGroupOp.SelectedItem;
                SQLCode.AppendLine(@"WhereGroup whereGroup2 = new WhereGroup();");
                SQLCode.AppendLine(@"whereGroup2.LogOperator = GroupLogicalOperators" + cboGroupOp.SelectedItem);

                WherePropertyContains whereProperty = new WherePropertyContains();
                ManagedProperty prop = (ManagedProperty)cboWhereProp21.SelectedItem;
                whereProperty.Property = prop.Name;
                whereProperty.SearchTerm = txtWhereText21.Text;
                SQLCode.AppendLine(@"WherePropertyContains whereProperty = new WherePropertyContains();");
                SQLCode.AppendLine(@"whereProperty.Property = """ + prop.Name + @""";");
                SQLCode.AppendLine(@"whereProperty.SearchTerm = """ + txtWhereText21.Text + @""";");

                whereGroup2.WhereProperties.Add(whereProperty);
                SQLCode.AppendLine(@"whereGroup2.WhereProperties.Add(whereProperty);");

                if (!string.IsNullOrEmpty(txtWhereText22.Text))
                {
                    whereProperty = new WherePropertyContains();
                    prop = (ManagedProperty)cboWhereProp22.SelectedItem;
                    whereProperty.Property = prop.Name;
                    whereProperty.SearchTerm = txtWhereText22.Text;
                    whereProperty.PropertyOperator = (LogicalOperators)cboWhereConnector21.SelectedItem;
                    SQLCode.AppendLine(@"WherePropertyContains whereProperty = new WherePropertyContains();");
                    SQLCode.AppendLine(@"whereProperty.Property = """ + prop.Name + @""";");
                    SQLCode.AppendLine(@"whereProperty.SearchTerm = """ + txtWhereText22.Text + @""";");
                    SQLCode.AppendLine(@"whereProperty.PropertyOperator = LogicalOperators." + cboWhereConnector21.SelectedItem.ToString() + ";");

                    whereGroup2.WhereProperties.Add(whereProperty);
                    SQLCode.AppendLine(@"whereGroup2.WhereProperties.Add(whereProperty);");

                    if (!string.IsNullOrEmpty(txtWhereText23.Text))
                    {
                        whereProperty = new WherePropertyContains();
                        prop = (ManagedProperty)cboWhereProp23.SelectedItem;
                        whereProperty.Property = prop.Name;
                        whereProperty.SearchTerm = txtWhereText23.Text;
                        whereProperty.PropertyOperator = (LogicalOperators)cboWhereConnector22.SelectedItem;
                        SQLCode.AppendLine(@"WherePropertyContains whereProperty = new WherePropertyContains();");
                        SQLCode.AppendLine(@"whereProperty.Property = """ + prop.Name + @""";");
                        SQLCode.AppendLine(@"whereProperty.SearchTerm = """ + txtWhereText23.Text + @""";");
                        SQLCode.AppendLine(@"whereProperty.PropertyOperator = LogicalOperators." + cboWhereConnector22.SelectedItem.ToString() + ";");

                        whereGroup2.WhereProperties.Add(whereProperty);
                        SQLCode.AppendLine(@"whereGroup2.WhereProperties.Add(whereProperty);");
                    }
                }
                whereStatement.WhereGroups.Add(whereGroup2);
                SQLCode.AppendLine(@"whereStatement.WhereGroups.Add(whereGroup2);");
            }

            foreach (ManagedProperty property in clbProperties.CheckedItems)
            {
                returnProperties.Add(property.Name);
                SQLCode.AppendLine(@"returnProperties.Add(""" + property.Name + @""");");
            }

            mossSearch.Where = whereStatement;
            selectStatement.ReturnProperties = returnProperties;
            mossSearch.SelectStatement = selectStatement;

            SQLCode.AppendLine(@"mossSearch.Where = whereStatement;");
            SQLCode.AppendLine(@"selectStatement.ReturnProperties = returnProperties;");
            SQLCode.AppendLine(@"mossSearch.SelectStatement = selectStatement;");

            //SortStatement sortStatement = new SortStatement();
            //sortStatement.SortProperty = sortProperty.ToString();
            //sortStatement.ResultSortOrder = SortDirection.Descending;
            //mossSearch.SortStatement = sortStatement;

            Debug.Write(mossSearch.ToString());
            txtSQLStatement.Text = mossSearch.ToString(true);
            txtSearchOMCode.Text = SQLCode.ToString();
        }

        private void btnExecuteSQL_Click_1(object sender, EventArgs e)
        {
            long average;
            errorProvider1.Clear();
            lblQueryError.Text = "";

            if (tabMain.SelectedTab == tabPgBuild) BuildSQL();
            DataTable resultsDataTable;

            if (radioOM.Checked)
                resultsDataTable = ExecuteOMSearch(1, out average);
            else if (radioWS.Checked)
                resultsDataTable = ExecuteWSSearch(1, out average);
            else
                resultsDataTable = ExecuteOMSearch(1, out average);

            gridResults.DataSource = resultsDataTable;
            lblPerformanceTests.Text = "Search took an average of: " + average + "ms or " + TimeSpan.FromMilliseconds(average).TotalSeconds + " seconds.";
            tabMain.SelectedTab = tabPgRun;
        }

        private DataTable ExecuteWSSearch(int rounds, out long average)
        {
            StringBuilder sb = new StringBuilder();

            sb.Append("<?xml version='1.0' encoding='utf-8' ?>");
            sb.Append("<QueryPacket xmlns='urn:Microsoft.Search.Query' Revision='1000'>");
            sb.Append("<Query domain='QDomain'>");
            sb.Append("<Range><StartAt>1</StartAt>");
            sb.Append("<Count>");
            sb.Append(txtMaxResults.Text);
            sb.Append("</Count></Range>");
            sb.Append("<SupportedFormats>");
            sb.Append("<Format>urn:Microsoft.Search.Response.Document.Document</Format>");
            sb.Append("</SupportedFormats>");
            sb.Append("<Context>");
            sb.Append("<QueryText language='en-US' type='MSSQLFT'>");
            sb.Append(txtSQLStatement.Text.TrimEnd('\r', '\n'));
            sb.Append("</QueryText>");
            sb.Append("</Context>");
            sb.Append("</Query>");
            sb.Append("</QueryPacket>");

            SearchService.QueryService searchService = new SearchService.QueryService();
            searchService.Credentials = System.Net.CredentialCache.DefaultCredentials;
            searchService.Url = txtWebApplicationUrl.Text + "/_vti_bin/search.asmx";


            string queryString = sb.ToString();

            long elapsedTime = 0;
            Stopwatch sw = new Stopwatch();
            DataSet ds = null;
            for (int i = 0; i < rounds; i++)
            {
                sw.Reset();
                sw.Start();
                ds = searchService.QueryEx(queryString);
                sw.Stop();
                lblPerformanceTests.Text = "Search Request " + i + " of " + rounds;
                lblPerformanceTests.Refresh();
                txtPerformanceReport.Text = txtPerformanceReport.Text + Environment.NewLine + "Search " + i + " | Time: " + sw.ElapsedMilliseconds + "ms | " + TimeSpan.FromMilliseconds(sw.ElapsedMilliseconds).TotalSeconds + " sec.";
                txtPerformanceReport.Refresh();
                elapsedTime = elapsedTime + sw.ElapsedMilliseconds;
            }

            average = elapsedTime / rounds;

            txtPerformanceReport.Text = txtPerformanceReport.Text + Environment.NewLine + " Total results in query " + ds.Tables[0].Rows.Count + ".";

            return ds.Tables[0];
        }


        private DataTable ExecuteOMSearch(int rounds, out long average)
        {
            SearchContext context;
            try
            {
                using (SPSite site = new SPSite(txtWebApplicationUrl.Text))
                {
                    context = SearchContext.GetContext(site);
                    FullTextSqlQuery qRequest = new FullTextSqlQuery(context.ServerContext);
                    qRequest.QueryText = txtSQLStatement.Text;
                    qRequest.ResultTypes = ResultType.RelevantResults;
                    qRequest.RowLimit = int.Parse(txtMaxResults.Text);

                    long elapsedTime = 0;
                    Stopwatch sw = new Stopwatch();
                    ResultTableCollection resultTables = null;
                    for (int i = 0; i < rounds; i++)
                    {
                        sw.Reset();
                        sw.Start();
                        resultTables = qRequest.Execute();
                        sw.Stop();
                        lblPerformanceTests.Text = "Search Request " + i + " of " + rounds;
                        lblPerformanceTests.Refresh();
                        txtPerformanceReport.Text = txtPerformanceReport.Text + Environment.NewLine + "Search " + i + " | Time: " + sw.ElapsedMilliseconds + "ms | " + TimeSpan.FromMilliseconds(sw.ElapsedMilliseconds).TotalSeconds + " sec.";
                        txtPerformanceReport.Refresh();
                        elapsedTime = elapsedTime + sw.ElapsedMilliseconds;
                    }
                    average = elapsedTime / rounds;

                    ResultTable relevantResults = resultTables[ResultType.RelevantResults];
                    DataTable resultsDataTable = new DataTable();
                    resultsDataTable.Load(relevantResults, LoadOption.OverwriteChanges);

                    txtPerformanceReport.Text = txtPerformanceReport.Text + Environment.NewLine + " Total results in query " + resultsDataTable.Rows.Count + ".";

                    return resultsDataTable;
                }
            }
            catch (QueryMalformedException exMalformed)
            {
                errorProvider1.SetError(btnExecuteSQL, "The SQL Query syntax has not been accepted by SharePoint");
                lblQueryError.Text = "The SQL Query syntax has not been accepted by SharePoint";
                average = 0;
                return null;
            }
            catch (Exception Ex)
            {
                errorProvider1.SetError(btnExecuteSQL, Ex.Message);
                lblQueryError.Text = Ex.Message;
                average = 0;
                return null;
            }
        }

        private void lblSQLCopy_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            Clipboard.SetText(txtSQLStatement.Text);
        }

        private void lblCopyOM_LinkClicked(object sender, LinkLabelLinkClickedEventArgs e)
        {
            Clipboard.SetText(txtSearchOMCode.Text);
        }

        private void txtSQLStatement_TextChanged(object sender, EventArgs e)
        {
            errorProvider1.Clear();
            lblQueryError.Text = "";
        }

        private void btnWhere1_Click(object sender, EventArgs e)
        {
            cboWhereConnector1.Visible = true;
            cboWhereProp2.Visible = true;
            txtWhereText2.Visible = true;
            btnWhere2.Visible = true;
            lblEq2.Visible = true;
        }

        private void btnWhere2_Click(object sender, EventArgs e)
        {
            cboWhereConnector2.Visible = true;
            cboWhereProp3.Visible = true;
            txtWhereText3.Visible = true;
            lblEq3.Visible = true;
        }

        private void btnWhere21_Click(object sender, EventArgs e)
        {
            cboWhereConnector21.Visible = true;
            cboWhereProp22.Visible = true;
            txtWhereText22.Visible = true;
            btnWhere22.Visible = true;
            lblEq22.Visible = true;
        }

        private void btnWhere22_Click_1(object sender, EventArgs e)
        {
            cboWhereConnector22.Visible = true;
            cboWhereProp23.Visible = true;
            txtWhereText23.Visible = true;
            lblEq23.Visible = true;
        }

        private DateTime GetLastCrawlDate()
        {
            using (SPSite site = new SPSite(txtWebApplicationUrl.Text))
            {
                SearchContext context = SearchContext.GetContext(site);
                Content sspContent = new Content(context);
                ContentSourceCollection sspContentSources = sspContent.ContentSources;

                ContentSource cs = sspContentSources["Local Office SharePoint Server sites"];

                return cs.CrawlCompleted;
            }
        }

        //private bool IsInChangeLog(DateTime FromDate, int ItemId, Guid ListId)
        //{
        //foreach (SPChange oChange in collChanges)
        //{
        //    SPChangeItem changeItem = (SPChangeItem)oChange;
        //    if (changeItem.Id == ItemId && changeItem.ListId == ListId)
        //        return true;
        //}
        //return false;
        //}

        private void btnGetChangeLog_Click(object sender, EventArgs e)
        {
            if (string.IsNullOrEmpty(txtChangeLogMinutes.Text)) return;

            grdChangeLog.Rows.Clear();

            using (SPSite site = new SPSite(txtWebApplicationUrl.Text))
            {
                DateTime dtDateStart = DateTime.Now.ToUniversalTime().Subtract(new TimeSpan(0, int.Parse(txtChangeLogMinutes.Text), 0));

                SPChangeToken myToken = new SPChangeToken(SPChangeCollection.CollectionScope.Site, site.ID, dtDateStart); // GetLastCrawlDate().ToUniversalTime()); //GetLastCrawlDate());
                SPChangeQuery oQuery = new SPChangeQuery(false, false);
                oQuery.Item = true;
                oQuery.Add = true;
                oQuery.Delete = true;
                oQuery.Update = true;
                oQuery.ChangeTokenStart = myToken;

                SPChangeCollection collChanges = site.GetChanges(oQuery);

                foreach (SPChange oChange in collChanges)
                {
                    try
                    {
                        SPChangeItem changeItem = (SPChangeItem)oChange;
                        SPListItem li = GetItem(site, changeItem.WebId, changeItem.ListId, changeItem.Id);
                        grdChangeLog.Rows.Add(changeItem.Time.ToString(), li.Name, li.Url.ToString(), changeItem.ChangeType.ToString());

                    }
                    catch (Exception)
                    {
                    }
                }
            }

        }

        private SPListItem GetItem(SPSite site, Guid WebID, Guid ListId, int ListItemId)
        {
            try
            {
                SPWeb web = site.OpenWeb(WebID);
                SPList list = web.Lists[ListId];
                return list.GetItemById(ListItemId);
            }
            catch (Exception)
            {
                return null;
            }
        }

        private void btnPerfTest_Click(object sender, EventArgs e)
        {
            long average;
            errorProvider1.Clear();
            lblQueryError.Text = "";

            //BuildSQL();
            tabMain.SelectedTab = tabPerformance;
            txtPerformanceReport.Text = "";

            DataTable resultsDataTable;

            if (radioOM.Checked)
                resultsDataTable = ExecuteOMSearch(int.Parse(txtPerfIterations.Text), out average);
            else if (radioWS.Checked)
                resultsDataTable = ExecuteWSSearch(int.Parse(txtPerfIterations.Text), out average);
            else
                resultsDataTable = ExecuteOMSearch(int.Parse(txtPerfIterations.Text), out average);

            gridResults.DataSource = resultsDataTable;
            lblPerformanceTests.Text = "Search took an average of: " + average + "ms or " + TimeSpan.FromMilliseconds(average).TotalSeconds + " seconds over " + txtPerfIterations.Text + " rounds.";
        }

        private void SearchCoder_Load(object sender, EventArgs e)
        {
            grdChangeLog.Columns.Add("Time", "Time");
            grdChangeLog.Columns.Add("Title", "Title");
            grdChangeLog.Columns.Add("Url", "Url");
            grdChangeLog.Columns.Add("Change", "Change");
        }
    }
}